import pymysql

DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = '123456'
DBNAME = 'mysever'


class Mainboard:
    def __init__(self, id, noProblem, screwDefect, ScrewLooseness, fanBreak, fanScratch, savePosition):
        # id
        self.__id = id
        # 主板没问题
        self.__noProblem = noProblem
        # 螺丝缺漏
        self.__screwDefect = screwDefect
        # 螺丝松动
        self.__ScrewLooseness = ScrewLooseness
        # 风扇未接
        self.__fanBreak = fanBreak
        # 风扇划伤
        self.__fanScratch = fanScratch
        # 图片保存位置
        self.__savePosition = savePosition

    def getId(self):  # 获取id
        return self.__id

    def getNoProblem(self):  # 获取是否问题
        return self.__noProblem

    def getScrewDefect(self):  # 获取螺丝是否缺漏
        return self.__screwDefect

    def getScrewLooseness(self):  # 获取螺丝是否松动
        return self.__ScrewLooseness

    def getFanBreak(self):  # 获取风扇是否未接
        return self.__fanBreak

    def getFanScratch(self):  # 获取风扇是否划伤
        return self.__fanScratch

    def getSavePosition(self):  # 获取图片保存信息
        return self.__savePosition



def insert(mainboard):
    try:
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        print('数据库连接成功!')
        cur = db.cursor()
        sqlQuery = "insert into mysever.serverinformation (id, noproblem, Screw_defect, Screw_Looseness, fan_break, fan_scratch, save_position) value (%s,%s,%s,%s,%s,%s,%s)"
        
        value = (
        mainboard.getId(), mainboard.getNoProblem(), mainboard.getScrewDefect(), mainboard.getScrewLooseness(),
        mainboard.getFanBreak(), mainboard.getFanScratch(), mainboard.getSavePosition())
        cur.execute(sqlQuery, value)
        db.commit()
        print('数据插入成功！')
    except pymysql.Error as e:
        print("数据插入失败：" + e)
        db.rollback()

    db.close()


def search(mainboard):
    try:
        id = mainboard.getId()
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        print('数据库连接成功!')
        cur = db.cursor()
        sqlQuery = "SELECT * FROM mysever.serverinformation where id = %s"
        value = (id)
        cur.execute(sqlQuery, value)
        results = cur.fetchall()
        for row in results:
            id = row[0]
            noProblem = row[1]
            screwDefect = row[2]
            ScrewLooseness = row[3]
            fanBreak = row[4]
            fanScratch = row[5]
            savePosition = row[6]

        print('id:%s,主板问题:%s,螺丝缺漏:%s,螺丝松动:%s,风扇未接:%s,风扇划伤:%s,保存位置:%s' % (
        id, noProblem, screwDefect, ScrewLooseness, fanBreak, fanScratch, savePosition))
        print('数据查询成功！')

    except pymysql.Error as e:
        print("数据查询失败：" + e)

    db.close()


def delete(mainboard):
    try:
        id = mainboard.getId()
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        print('数据库连接成功!')
        cur = db.cursor()
        sqlQuery = "DELETE FROM mysever.serverinformation where id = %s"
        value = (id)
        cur.execute(sqlQuery, value)
        db.commit()
        print('数据删除成功！')
    except pymysql.Error as e:
        print("数据删除失败：" + e)
        db.rollback()

    db.close()


def show():
    try:
        db = pymysql.connect(host=DBHOST, user=DBUSER, password=DBPASS, database=DBNAME)
        print('数据库连接成功!')
        cur = db.cursor()
        sqlQuery = "SELECT * FROM mysever.serverinformation"
        cur.execute(sqlQuery)
        results = cur.fetchall()
        if len(results) == 0:
            print("没有数据！！！")
            return "There is no data"
        else:
            format_title = '{:^6}\t{:^8}\t{:^8}\t{:^8}\t{:^8}\t{:^8}\t{:^8}'
            print(format_title.format('id','主板问题', '螺丝缺漏', '螺丝松动', '风扇未接', '风扇划伤', '保存位置'))
            format_data = '{:^6}\t{:^8}\t{:^8}\t{:^8}\t{:^8}\t{:^8}\t{:^8}'
            for row in results:
                print(format_data.format(
                    row[0],
                    row[1],
                    row[2],
                    row[3],
                    row[4],
                    row[5],
                    row[6]
                ))
            print('数据查询成功！')
            return results
    except pymysql.Error as e:
        print("数据查询失败：" + e)

    db.close()
